IF exists(SELECT * FROM dbo.sysobjects WHERE name='r_maandGemiddeldeblad' AND xtype='FN') 
BEGIN
	DROP FUNCTION r_maandGemiddeldeblad
END
GO

CREATE       function r_maandGemiddeldeblad
(@klas varchar(5),
 @periode int)
 returns @resultaten table(v1 varchar(2),p1 int,
	v2 varchar(2),p2 int,
	v3 varchar(2),p3 int,
	v4 varchar(2),p4 int,
	v5 varchar(2),p5 int,
	v6 varchar(2),p6 int,
	v7 varchar(2),p7 int,
	v8 varchar(2),p8 int,
	v9 varchar(2),p9 int,
	v10 varchar(2),p10 int,
	v11 varchar(2),p11 int,
	v12 varchar(2),p12 int,
	v13 varchar(2),p13 int,
	v14 varchar(2),p14 int,
	v15 varchar(2),p15 int,
	v16 varchar(2),p16 int,
	v17 varchar(2),p17 int,
	v18 varchar(2),p18 int,
	v19 varchar(2),p19 int,
	v20 varchar(2),p20 int) 
as
begin
-- Aanmaak en vullen tabel #vakken

declare @vakken table(volgNummer char(2),vakcode varchar(2))
declare @groepen table(groepscode varchar(5))

insert into @vakken(volgNummer,vakcode)
select distinct volgnummer,vakcode 
  from leraars.dbo.alleKlasVakcodes(@klas)
    where not vakcode like 'ES'
                     and not( vakcode like 'SE')
			and not( vakcode like 'KV')
-- Invoegen van n record
insert into @resultaten(v1)
values(null)

-- Aanvullen van de namen van de vakken in de juiste volgorde. Het leerlingtotaal
-- krijgt de aanduiding 'AT'

declare deCursor cursor local for
select distinct vakcode,volgnummer
  from @vakken order by volgNummer

declare @vak varchar(5)
declare @volgNr int
declare @vakNr int
declare @zin varchar(200)

open deCursor
set @vakNr = 1
fetch next from deCursor into @vak,@volgNr 
while (@@fetch_status=0) and (@vakNr < 20)
begin
  if @vakNr = 1 update @resultaten set v1 = @vak
  if @vakNr = 2 update @resultaten set v2 = @vak
  if @vakNr = 3 update @resultaten set v3 = @vak
  if @vakNr = 4 update @resultaten set v4 = @vak
  if @vakNr = 5 update @resultaten set v5 = @vak
  if @vakNr = 6 update @resultaten set v6 = @vak
  if @vakNr = 7 update @resultaten set v7 = @vak
  if @vakNr = 8 update @resultaten set v8 = @vak
  if @vakNr = 9 update @resultaten set v9 = @vak
  if @vakNr = 10 update @resultaten set v10 = @vak
  if @vakNr = 11 update @resultaten set v11 = @vak
  if @vakNr = 12 update @resultaten set v12 = @vak
  if @vakNr = 13 update @resultaten set v13 = @vak
  if @vakNr = 14 update @resultaten set v14 = @vak
  if @vakNr = 15 update @resultaten set v15 = @vak
  if @vakNr = 16 update @resultaten set v16 = @vak
  if @vakNr = 17 update @resultaten set v17 = @vak
  if @vakNr = 18 update @resultaten set v18 = @vak
  if @vakNr = 19 update @resultaten set v19 = @vak
  set @vakNr = @vakNr + 1
  fetch next from deCursor into @vak,@volgNr
end
deallocate deCursor
update @resultaten set v20 = 'AT'

--Aanvullen van de maandtotalen

declare @maandcijfers table(
vak char(2),
procent int)

insert into @maandcijfers(vak,procent)
select vak,procent from r_maandVakgemiddelde(@klas,@periode)

--Aanvullen van de klastotalen

insert into @maandcijfers(vak,procent)
select vak,procent from r_maandKlastotaal(@klas,@periode)

update @resultaten
set p1 = case when m1.procent < 0 then null else m1.procent end ,
    p2 = case when m2.procent < 0 then null else m2.procent end ,
    p3 = case when m3.procent < 0 then null else m3.procent end,
    p4 = case when m4.procent < 0 then null else m4.procent end,
    p5 = case when m5.procent < 0 then null else m5.procent end,
    p6 = case when m6.procent < 0 then null else m6.procent end,
    p7 = case when m7.procent < 0 then null else m7.procent end,
    p8 = case when m8.procent < 0 then null else m8.procent end,
    p9 = case when m9.procent < 0 then null else m9.procent end,
    p10 = case when m10.procent < 0 then null else m10.procent end,
    p11 = case when m11.procent < 0 then null else m11.procent end,
    p12 = case when m12.procent < 0 then null else m12.procent end,
    p13 = case when m13.procent < 0 then null else m13.procent end,
    p14 = case when m14.procent < 0 then null else m14.procent end,
    p15 = case when m15.procent < 0 then null else m15.procent end,
    p16 = case when m16.procent < 0 then null else m16.procent end,
    p17 = case when m17.procent < 0 then null else m17.procent end,
    p18 = case when m18.procent < 0 then null else m18.procent end,
    p19 = case when m19.procent < 0 then null else m19.procent end,
    p20 = case when m20.procent < 0 then null else m20.procent end
from @resultaten as r
  left join (select procent,vak from @maandcijfers) as m1
	on m1.vak = r.v1
  left join (select procent,vak from @maandcijfers) as m2
	on  m2.vak = r.v2
  left join (select procent,vak from @maandcijfers) as m3
	on m3.vak = r.v3
  left join (select procent,vak from @maandcijfers) as m4
	on m4.vak = r.v4
  left join (select procent,vak from @maandcijfers) as m5
	on m5.vak = r.v5
  left join (select procent,vak from @maandcijfers) as m6
	on m6.vak = r.v6
  left join (select procent,vak from @maandcijfers) as m7
	on m7.vak = r.v7
  left join (select procent,vak from @maandcijfers) as m8
	on m8.vak = r.v8
  left join (select procent,vak from @maandcijfers) as m9
	on m9.vak = r.v9
  left join (select procent,vak from @maandcijfers) as m10
	on m10.vak = r.v10
  left join (select procent,vak from @maandcijfers) as m11
	on m11.vak = r.v11
  left join (select procent,vak from @maandcijfers) as m12
	on m12.vak = r.v12
  left join (select procent,vak from @maandcijfers) as m13
	on m13.vak = r.v13
  left join (select procent,vak from @maandcijfers) as m14
	on m14.vak = r.v14
  left join (select procent,vak from @maandcijfers) as m15
	on m15.vak = r.v15
  left join (select procent,vak from @maandcijfers) as m16
	on m16.vak = r.v16
  left join (select procent,vak from @maandcijfers) as m17
	on m17.vak = r.v17
  left join (select procent,vak from @maandcijfers) as m18
	on m18.vak = r.v18
  left join (select procent,vak from @maandcijfers) as m19
	on m19.vak = r.v19
  left join (select procent,vak from @maandcijfers) as m20
	on m20.vak = r.v20
return
end

-------------------------
--  Rapporten 
-------------------------